﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Web.UI.WebControls;


public partial class Main_Reports_Accounts_AccountBooks_LedgerDetails : System.Web.UI.Page
{
    Datautility DU = new Datautility();
    string strSql = string.Empty;
    double dblDebit = 0;
    double dblCredit = 0;
    double dblTotalBalance = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["userid"] == null)
        {
            Response.Redirect("~/SessionExpired.aspx");
        }
        if (!IsPostBack)
        {
            //**************Set Minimum and maximum date calender Control********
            AccountSettings.setMinDate(form1);
            //********************End **********
            if (Request.QueryString != null)
            {
               hidLedgerId.Value = Request.QueryString["LID"].ToString();
               hidLedgerName.Value = Request.QueryString["LNAME"].ToString();
               hidFromDate.Value = Request.QueryString["FROMDATE"].ToString();
               hidToDate.Value = Request.QueryString["TODATE"].ToString();
               BindLedger();
            }
            //_BindLedger();
        }
    }
     protected void BindLedger()
    {
        DataTable DT = _GetLedgerDetails(hidLedgerId.Value,hidLedgerName.Value,hidFromDate.Value,hidToDate.Value);
        grdDisplay.DataSource = DT;
        grdDisplay.DataBind();
        //monthlyChart.Visible = true;
        //monthlyChart.DataSource = DT;
       // monthlyChart.DataBind();
        //monthlyChart.ChartAreas.FindByName("ChartArea1").AxisX.Title = "[ Financial Year " + Session["FINYEAR"].ToString() + "-" + (Convert.ToInt32(Session["FINYEAR"].ToString()) + 1).ToString().Substring(2) + " ]";
    }
    private DataTable _GetLedgerDetails(string strLID, string strLNAME, string from, string to)
    {
        string strFinyear = Session["finyear"].ToString();
        DataTable DTMain = new DataTable();
        DTMain.Columns.Add("MonthName", typeof(string));
        DTMain.Columns.Add("Debit", typeof(string));
        DTMain.Columns.Add("Credit", typeof(string));
        DTMain.Columns.Add("FromDate", typeof(string));
        DTMain.Columns.Add("ToDate", typeof(string));
        DTMain.Columns.Add("BalanceForChart", typeof(string));

        if (strLNAME != string.Empty && strLID != string.Empty && from != string.Empty && to != string.Empty)
        {
            string strLedgerType = _IsDebit_CreditLedgerType(strLID);
            DateTimeFormatInfo dtFormat = new CultureInfo("en-GB", false).DateTimeFormat;

            int intFinyear = Convert.ToInt32(strFinyear);
            string strStartDate = from;
            string strEndDate = to;
            string[] fromN = from.Split('/');
            string[] toN = to.Split('/');
            string strStartDateN = fromN[1] + "/" + fromN[0] + "/" + fromN[2];
            string strEndDateN = toN[1] + "/" + toN[0] + "/" + toN[2];
            DateTime dtStartDate = Convert.ToDateTime(strStartDate);
            DateTime dtEndDate = Convert.ToDateTime(strEndDate);
            lblHeading.Text = "Monthly Transactions of " + strLNAME + " Account from " + dtStartDate.ToString("dd-MMM-yyyy") + " To " + dtEndDate.ToString("dd-MMM-yyyy");

            int intStartMonth = dtStartDate.Month;
            int intStartYear = dtStartDate.Year;
            int intEndMonth = dtEndDate.Month;
            int intEndYear = dtEndDate.Year;
            DateTime dtTempDate = Convert.ToDateTime("01/" + intStartMonth.ToString() + "/" + intStartYear.ToString());

            do
            {
                DataRow drMain = DTMain.NewRow();
                string strMonthName = string.Empty;
                double dblDebitAmt = 0;
                double dblCreditAmt = 0;
                double dblBalnceAmtForChart = 0;
                if (intStartMonth == intEndMonth && intStartYear == intEndYear)
                {
                    strMonthName = dtFormat.GetMonthName(dtTempDate.Month);
                    strStartDate = dtTempDate.Month.ToString() + "/" + dtStartDate.Day.ToString() + "/" + dtTempDate.Year.ToString();
                    strEndDate = dtEndDate.Month.ToString() + "/" + dtEndDate.Day.ToString() + "/" + dtEndDate.Year.ToString();
                }
                else if (intStartMonth != intEndMonth && intStartYear == intEndYear)
                {
                    if (dtTempDate.Month == intStartMonth)
                    {
                        strMonthName = dtFormat.GetMonthName(dtTempDate.Month);
                        strStartDate = dtStartDate.Month.ToString() + "/" + dtStartDate.Day.ToString() + "/" + dtStartDate.Year.ToString();
                        strEndDate = dtStartDate.Month.ToString() + "/" + DateTime.DaysInMonth(dtStartDate.Year, dtStartDate.Month).ToString() + "/" + dtStartDate.Year.ToString();
                    }
                    else if (dtTempDate.Month == intEndMonth)
                    {
                        strMonthName = dtFormat.GetMonthName(dtTempDate.Month);
                        strStartDate = dtTempDate.Month.ToString() + "/01/" + dtTempDate.Year.ToString();
                        strEndDate = dtTempDate.Month.ToString() + "/" + dtEndDate.Day.ToString() + "/" + dtTempDate.Year.ToString();
                    }
                    else
                    {
                        strMonthName = dtFormat.GetMonthName(dtTempDate.Month);
                        strStartDate = dtTempDate.Month.ToString() + "/01/" + dtTempDate.Year.ToString();
                        strEndDate = dtTempDate.Month.ToString() + "/" + DateTime.DaysInMonth(dtTempDate.Year, dtTempDate.Month).ToString() + "/" + dtTempDate.Year.ToString();
                    }
                }
                else if (intStartYear < intEndYear)
                {
                    if ((dtTempDate.Month == intStartMonth) && (dtTempDate.Year == intStartYear))
                    {
                        strMonthName = dtFormat.GetMonthName(dtTempDate.Month);
                        strStartDate = dtStartDate.Month.ToString() + "/" + dtStartDate.Day.ToString() + "/" + dtStartDate.Year.ToString();
                        strEndDate = dtStartDate.Month.ToString() + "/" + DateTime.DaysInMonth(dtStartDate.Year, dtStartDate.Month).ToString() + "/" + dtStartDate.Year.ToString();
                    }
                    else if ((dtTempDate.Month == intEndMonth) && (dtTempDate.Year == intEndYear))
                    {
                        strMonthName = dtFormat.GetMonthName(dtTempDate.Month);
                        strStartDate = dtTempDate.Month.ToString() + "/01/" + dtTempDate.Year.ToString();
                        strEndDate = dtTempDate.Month.ToString() + "/" + dtEndDate.Day.ToString() + "/" + dtTempDate.Year.ToString();
                    }
                    else
                    {
                        strMonthName = dtFormat.GetMonthName(dtTempDate.Month);
                        strStartDate = dtTempDate.Month.ToString() + "/01/" + dtTempDate.Year.ToString();
                        strEndDate = dtTempDate.Month.ToString() + "/" + DateTime.DaysInMonth(dtTempDate.Year, dtTempDate.Month).ToString() + "/" + dtTempDate.Year.ToString();
                    }
                }
                SqlParameter[] sparamDebit ={
                                                new SqlParameter("@FROMDATE",strStartDate),
                                                new SqlParameter("@TODATE",strEndDate),
                                                new SqlParameter("@LID",strLID),
                                                new SqlParameter("@INCLUDEOPTVOUCHERS","1"),
                                                new SqlParameter("@ISDEBITORCREDIT","1"),
                                                new SqlParameter("@ISCLOSINGAMOUNT","0"),
                                                new SqlParameter("@FINYEAR",strFinyear),
                                                new SqlParameter("@ISFIRSTYEAR","0")
                                            };
                DataTable DTDebitAmt = DU.getdatausingprocedure("USP_ACC_GetLedgerTotal", sparamDebit);
                if (DTDebitAmt.Rows.Count > 0)
                {
                    if (DTDebitAmt.Rows[0][0].ToString() != string.Empty)
                    {
                        dblDebitAmt = Convert.ToDouble(DTDebitAmt.Rows[0][0].ToString());
                    }
                }
                SqlParameter[] sparamCredit ={
                                                new SqlParameter("@FROMDATE",strStartDate),
                                                new SqlParameter("@TODATE",strEndDate),
                                                new SqlParameter("@LID",strLID),
                                                new SqlParameter("@INCLUDEOPTVOUCHERS","1"),
                                                new SqlParameter("@ISDEBITORCREDIT","0"),
                                                new SqlParameter("@ISCLOSINGAMOUNT","0"),
                                                new SqlParameter("@FINYEAR",strFinyear),
                                                new SqlParameter("@ISFIRSTYEAR","0")
                                            };
                DataTable DTCreditAmt = DU.getdatausingprocedure("USP_ACC_GetLedgerTotal", sparamCredit);
                if (DTCreditAmt.Rows.Count > 0)
                {
                    if (DTCreditAmt.Rows[0][0].ToString() != string.Empty)
                    {
                        dblCreditAmt = Convert.ToDouble(DTCreditAmt.Rows[0][0].ToString());
                    }
                }
                dblBalnceAmtForChart = dblDebitAmt + dblCreditAmt;
                if (strLedgerType.ToUpper() == "EXPENSES")
                {
                    if (dblBalnceAmtForChart > 0)
                    {
                        dblBalnceAmtForChart = 0 - dblBalnceAmtForChart;
                    }
                    else
                    {
                        dblBalnceAmtForChart = Math.Abs(dblBalnceAmtForChart);
                    }
                }
               
                drMain["MonthName"] = strMonthName + "-" + dtTempDate.Year.ToString().Substring(2);
                drMain["Debit"] = dblDebitAmt.ToString("0.00");
                drMain["Credit"] = dblCreditAmt.ToString("0.00");
                drMain["FromDate"] = strStartDate;
                drMain["ToDate"] = strEndDate;
                drMain["BalanceForChart"] = dblBalnceAmtForChart.ToString("0.00");
                DTMain.Rows.Add(drMain);
               
                dtTempDate = dtTempDate.AddMonths(1);

            } while (dtTempDate <= dtEndDate);
        }
      
        return DTMain;
    }
    private string _IsFirstYear(string strFinYear)
    {
        string strIsFirstYear = string.Empty;
        strSql = "SELECT MIN(FINYEAR)AS FINYEAR FROM MAS_FINYEAR";
        DataTable DTMinFinyear = DU.getdata(strSql);
        if (DTMinFinyear.Rows.Count > 0)
        {
            if (Convert.ToInt32(DTMinFinyear.Rows[0]["FINYEAR"].ToString()) == Convert.ToInt32(strFinYear))
            {
                strIsFirstYear = "1";
            }
            else
            {
                strIsFirstYear = "0";
            }
        }
        return strIsFirstYear;
    }
    private string _IsDebit_CreditLedgerType(string strLID)
    {
        string strLedgerType = string.Empty;
        if (strLID != string.Empty)
        {
            SqlParameter[] sparam ={
                                        new SqlParameter("@LID",strLID)
                                               
                                     };
            DataTable DTLedgerType = DU.getdatausingprocedure("USP_ACC_GetLedgerTypeForChart", sparam);
            if (DTLedgerType.Rows.Count > 0)
            {
                strLedgerType = DTLedgerType.Rows[0]["RESULT"].ToString();
            }
        }
        return strLedgerType;
    }
 
    
    protected void grdDisplay_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            string strOpeningBalance = string.Empty;

            //****************Opening Balance*******//
            double dblOpeningBalance = _GetOpeningBalance(hidLedgerId.Value, hidFromDate.Value);
            if (dblOpeningBalance < 0)
            {
                strOpeningBalance = Convert.ToDouble(string.Format("{0:0.00}", Math.Abs(Convert.ToDouble(dblOpeningBalance)))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "") + " Dr";
            }
            else if (dblOpeningBalance > 0)
            {
                strOpeningBalance = Convert.ToDouble(string.Format("{0:0.00}", Math.Abs(Convert.ToDouble(dblOpeningBalance)))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "") + " Cr";
            }
            else
            {
                strOpeningBalance = "";
            }
            string NewRow = "</td></tr><tr style=\"background:#ececec; font-weight:bold;\"><td>&nbsp;</td><td>&nbsp;</td><td style=\"text-align:right;\">Opening Balance</td><td style=\"text-align:right;\">" + strOpeningBalance.ToString();
            e.Row.Cells[3].Text = e.Row.Cells[3].Text + " " + NewRow;

            dblTotalBalance += dblOpeningBalance;
        }
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //***************Display Format and get total ****//
            Label lblDebit = (Label)e.Row.FindControl("lblDebit");
            Label lblCredit = (Label)e.Row.FindControl("lblCredit");
            Label lblBalance = (Label)e.Row.FindControl("lblBalance");
            HyperLink hyperMonth = (HyperLink)e.Row.FindControl("hyperMonth");
            HiddenField hidDebit = (HiddenField)e.Row.FindControl("hidDebit");
            HiddenField hidCredit = (HiddenField)e.Row.FindControl("hidCredit");
            HiddenField hidFromDate = (HiddenField)e.Row.FindControl("hidFromDate");
            HiddenField hidToDate = (HiddenField)e.Row.FindControl("hidToDate");

            hyperMonth.Attributes.Add("onclick", "return OpenMonthlySummary('" + hidLedgerId.Value + "','" + hidLedgerName.Value + "','" + hidFromDate.Value + "','" + hidToDate.Value + "')");

            if (Math.Abs(Convert.ToDouble(hidDebit.Value)) == 0)
            {
                lblDebit.Text = "";
            }
            else
            {
                lblDebit.Text = Convert.ToDouble(string.Format("{0:0.00}", Math.Abs(Convert.ToDouble(hidDebit.Value)))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "");
            }
            if (Math.Abs(Convert.ToDouble(hidCredit.Value)) == 0)
            {
                lblCredit.Text = "";
            }
            else
            {
                lblCredit.Text = Convert.ToDouble(string.Format("{0:0.00}", Math.Abs(Convert.ToDouble(hidCredit.Value)))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "");
            }
            dblDebit = Convert.ToDouble(hidDebit.Value);
            dblCredit = Convert.ToDouble(hidCredit.Value);
            dblTotalBalance += dblDebit + dblCredit;
            if (dblTotalBalance < 0)
            {
                lblBalance.Text = Convert.ToDouble(string.Format("{0:0.00}", Math.Abs(Convert.ToDouble(dblTotalBalance)))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "") + " Dr";
            }
            else if (dblTotalBalance > 0)
            {
                lblBalance.Text = Convert.ToDouble(string.Format("{0:0.00}", Math.Abs(Convert.ToDouble(dblTotalBalance)))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "") + " Cr";
            }
            else
            {
                lblBalance.Text = "";
            }
            //***************End Display Format and get total ****//

        }
        if (e.Row.RowType == DataControlRowType.Footer)
        {

            string strClosingBalance = string.Empty;
            //***************Closing Balance************//
            double dblClosingBalance = dblTotalBalance + dblDebit + dblCredit;
            if (dblTotalBalance < 0)
            {
                strClosingBalance = Convert.ToDouble(string.Format("{0:0.00}", Math.Abs(Convert.ToDouble(dblTotalBalance)))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "") + " Dr";
            }
            else if (dblClosingBalance > 0)
            {
                strClosingBalance = Convert.ToDouble(string.Format("{0:0.00}", Math.Abs(Convert.ToDouble(dblTotalBalance)))).ToString("c", new CultureInfo("hi-IN")).Replace("रु", "") + " Cr";
            }
            else
            {
                strClosingBalance = string.Empty;
            }
            e.Row.Style.Add("background", "#ececec");
            e.Row.Cells[2].Text = "Closing Balance";
            e.Row.Cells[3].Text = strClosingBalance.ToString();
            e.Row.Cells[2].Font.Bold = true;
            e.Row.Cells[3].Font.Bold = true;
            e.Row.Cells[2].HorizontalAlign = HorizontalAlign.Right;
            e.Row.Cells[3].HorizontalAlign = HorizontalAlign.Right;
        }
    }
    private double _GetOpeningBalance(string strLID, string strDate)
    {
        double dblOpenBalance = 0;
        if (strLID != string.Empty && strDate != string.Empty)
        {
            string[] strArr = strDate.Split('/');
            strDate = strArr[1] + "/" + strArr[0] + "/" + strArr[2];

            SqlParameter[] sparam ={
                                       new SqlParameter("@LID",strLID),
                                       new SqlParameter("@DATE",strDate),
                                       new SqlParameter("@ISFIRSTYEAR",_IsFirstYear(_GetFinyear(strDate))),
                                       new SqlParameter("@FINYEAR",_GetFinyear(strDate))
                                   };
            DataTable DTOpenBal = DU.getdatausingprocedure("USP_ACC_GetLedgerOpeningBalance", sparam);
            if (DTOpenBal.Rows.Count > 0)
            {
                if (DTOpenBal.Rows[0]["OPENINGBALANCE"].ToString() != string.Empty)
                {
                    dblOpenBalance = Convert.ToDouble(DTOpenBal.Rows[0]["OPENINGBALANCE"].ToString());
                }
            }
        }
        return dblOpenBalance;
    }
    private string _GetFinyear(string strDate)
    {
        string strFinyear = Session["FINYEAR"].ToString();
        if (strDate != string.Empty)
        {
            string[] strArr = strDate.Split('/');
            int intMonth = Convert.ToInt32(strArr[0].ToString());
            int intYear = Convert.ToInt32(strArr[2].ToString());
            if (intMonth >= 4 && intMonth <= 12)
            {
                strFinyear = intYear.ToString();
            }
            else if (intMonth >= 1 && intMonth <= 3)
            {
                strFinyear = (intYear - 1).ToString();
            }
        }
        return strFinyear;
    }
}
